import os
import sys
from pathlib import Path
from datetime import datetime, timedelta
import random

# Add the project root to the Python path
sys.path.append(str(Path(__file__).resolve().parent.parent))

from database.db_connection import execute_query, execute_many

def insert_test_data():
    """Insert test data into the database for testing purposes"""
    print("Inserting test data...")
    
    # Insert test employees
    employees = [
        ('EMP001', 'Zhang Wei', 'Engineering', 'Software Engineer', True),
        ('EMP002', 'Li Na', 'Marketing', 'Marketing Manager', False),
        ('EMP003', 'Wang Fei', 'HR', 'HR Specialist', True),
        ('EMP004', 'Liu Yang', 'Finance', 'Accountant', False),
        ('EMP005', 'Chen Jie', 'Engineering', 'Senior Developer', True),
    ]
    
    employee_query = """
        INSERT INTO employees (employee_id, name, department, position, face_registered)
        VALUES (%s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE 
            name = VALUES(name),
            department = VALUES(department),
            position = VALUES(position),
            face_registered = VALUES(face_registered)
    """
    
    execute_many(employee_query, employees)
    print(f"Inserted {len(employees)} employees")
    
    # 检查是否已经有考勤记录，避免重复插入
    count_query = "SELECT COUNT(*) as count FROM attendance_records"
    result = execute_query(count_query)
    attendance_count = result[0]['count'] if result else 0
    
    # 仅当没有考勤记录时才插入
    if attendance_count == 0:
        # Insert test attendance records
        today = datetime.now().date()
        attendance_records = []
        
        # Generate attendance for the last 7 days
        for i in range(7):
            date = today - timedelta(days=i)
            for emp_id, _, _, _, _ in employees:
                # Skip some records randomly to simulate absences
                if random.random() > 0.9:
                    continue
                    
                # Generate random check-in time between 8:00 and 9:30
                hour = 8
                minute = random.randint(0, 90)
                if minute >= 60:
                    hour += 1
                    minute -= 60
                
                check_in = datetime.combine(date, datetime.min.time().replace(hour=hour, minute=minute))
                
                # Generate random check-out time between 17:00 and 18:30
                hour = 17
                minute = random.randint(0, 90)
                if minute >= 60:
                    hour += 1
                    minute -= 60
                    
                check_out = datetime.combine(date, datetime.min.time().replace(hour=hour, minute=minute))
                
                # Determine status based on check-in time
                status = 'normal'
                if check_in.hour >= 9 and check_in.minute > 0:
                    status = 'late'
                elif check_out.hour < 17:
                    status = 'early_leave'
                    
                attendance_records.append((emp_id, check_in, check_out, status, ''))
        
        attendance_query = """
            INSERT INTO attendance_records (employee_id, check_in_time, check_out_time, status, note)
            VALUES (%s, %s, %s, %s, %s)
        """
        
        execute_many(attendance_query, attendance_records)
        print(f"Inserted {len(attendance_records)} attendance records")
    else:
        print(f"Skipped inserting attendance records, already have {attendance_count} records")
    
    # 检查是否已经有请假申请记录，避免重复插入
    count_query = "SELECT COUNT(*) as count FROM leave_requests"
    result = execute_query(count_query)
    leave_count = result[0]['count'] if result else 0
    
    # 仅当没有请假申请记录时才插入
    if leave_count == 0:
        # 首先检查每个员工ID是否存在
        existing_employees = []
        for emp_id, _, _, _, _ in employees:
            check_query = "SELECT employee_id FROM employees WHERE employee_id = %s"
            result = execute_query(check_query, (emp_id,))
            if result and len(result) > 0:
                existing_employees.append(emp_id)
        
        if not existing_employees:
            print("没有找到可用的员工ID，跳过请假申请的添加")
        else:
            # Insert test leave requests
            today = datetime.now().date()
            leave_types = ['sick', 'personal', 'vacation', 'other']
            statuses = ['pending', 'approved', 'rejected']
            leave_requests = []
            
            for i in range(10):
                # 只从确认存在的员工中随机选择
                emp_id = random.choice(existing_employees)
                
                start_date = today + timedelta(days=random.randint(1, 30))
                duration = random.randint(1, 5)
                end_date = start_date + timedelta(days=duration)
                
                leave_type = random.choice(leave_types)
                status = random.choice(statuses)
                reason = f"Test leave request {i+1}"
                
                leave_requests.append((emp_id, leave_type, start_date, end_date, reason, status, ''))
            
            leave_query = """
                INSERT INTO leave_requests 
                (employee_id, leave_type, start_date, end_date, reason, status, admin_comment)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            
            execute_many(leave_query, leave_requests)
            print(f"Inserted {len(leave_requests)} leave requests")
    else:
        print(f"Skipped inserting leave requests, already have {leave_count} records")
    
    # 检查是否已经有制度记录，避免重复插入
    count_query = "SELECT COUNT(*) as count FROM policies"
    result = execute_query(count_query)
    policy_count = result[0]['count'] if result else 0
    
    # 仅当没有制度记录时才插入
    if policy_count == 0:
        # Insert test policies
        policies = [
            ('考勤制度', 'attendance', '所有员工必须在上午9:00前签到，下午5:00后签退。迟到或早退将影响绩效考核。\n\n迟到超过三次将被扣除当月绩效奖金的10%。\n\n连续旷工三天及以上，或一个月内累计旷工五天及以上者，公司有权解除劳动合同。'),
            ('请假制度', 'leave', '员工每年享有15天年假和10天病假。请假需提前申请并获得主管批准。\n\n病假超过三天需提供医院证明。\n\n事假需提前三天申请，特殊情况除外。\n\n婚假为10天，产假按国家及地方规定执行。'),
            ('远程工作制度', 'general', '员工每周可在家工作最多2天，需获得经理批准。远程工作不影响工作职责和效率要求。\n\n远程工作期间需保持通讯畅通，及时响应工作消息。\n\n特殊情况下，公司可能会要求员工回到办公室工作。')
        ]
        
        policy_query = """
            INSERT INTO policies (title, category, content)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE 
            title = VALUES(title),
            category = VALUES(category),
            content = VALUES(content)
        """
        
        execute_many(policy_query, policies)
        print(f"Inserted {len(policies)} policies")
    else:
        print(f"Skipped inserting policies, already have {policy_count} records")
    
    print("Test data insertion complete!")

if __name__ == "__main__":
    insert_test_data() 